from sqlalchemy                          import create_engine
from sqlalchemy                          import Table
from sqlalchemy                          import Float
from sqlalchemy                          import Integer
from sqlalchemy                          import Date
from sqlalchemy                          import Column
from sqlalchemy                          import Boolean
from sqlalchemy                          import String
from sqlalchemy                          import MetaData
from sqlalchemy                          import ForeignKey
from sqlalchemy.orm                      import mapper
from sqlalchemy.orm                      import sessionmaker
from sqlalchemy.orm                      import backref
from sqlalchemy.orm                      import relationship

from Entities.Calendars.Calendar         import Calendar
from Entities.Calendars.SimpleCalendar   import SimpleCalendar
from Entities.Calendars.UnionCalendar    import UnionCalendar
from Entities.Calendars.Holiday          import Holiday
from Entities.Calendars.SimpleHoliday    import SimpleHoliday
from Entities.Calendars.RepeatingHoliday import RepeatingHoliday

from Entities.Products.Product           import Product
from Entities.Products.Currency          import Currency
from Entities.Products.Stock             import Stock
from Entities.Products.Dividend          import Dividend

from Entities.Tags.TagSpecification      import TagSpecification
from Entities.Tags.Tag                   import Tag

engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
 
Session = sessionmaker(bind=engine)
session = Session()

calendars_table = Table \
    (
    'calendars',
    metadata,
    Column ('id',            Integer, primary_key=True),
    Column ('code',          String,  nullable=False, unique=True),
    Column ('name',          String,  nullable=False, unique=True),
    Column ('calendar_type', String (20), nullable=False),
    Column ('monday',        Boolean),
    Column ('tuesday',       Boolean),
    Column ('wednesday',     Boolean),
    Column ('thurday',       Boolean),
    Column ('friday',        Boolean),
    Column ('saturday',      Boolean),
    Column ('sunday',        Boolean)
    ) 
    
calendar_children_table = Table \
    (
    'calendar_children',
    metadata,
    Column ('parent_id', Integer, ForeignKey ('calendars.id')),
    Column ('child_id',  Integer, ForeignKey ('calendars.id'))
    )
    
calendar_mapper = \
    mapper \
        (
        Calendar, 
        calendars_table, 
        polymorphic_on=calendars_table.c.calendar_type, 
        polymorphic_identity="calendar"
        )
        
simple_calendar_mapper = \
    mapper \
        (
        SimpleCalendar, 
        inherits=calendar_mapper, 
        polymorphic_identity='simple',
        properties=
            {
            'holidays':relationship
                (
                Holiday,  
                backref='calendar'
                )
            }
        )
        
union_calendar_mapper  = \
    mapper \
        (
        UnionCalendar,  
        inherits=calendar_mapper, 
        polymorphic_identity='union',
        properties = \
            {
            'children':relationship \
                (
                Calendar, 
                secondary=calendar_children_table,
                primaryjoin   = calendar_children_table.c.parent_id == calendars_table.c.id,
                secondaryjoin = calendar_children_table.c.child_id  == calendars_table.c.id
                )
            }
        )

holidays_table = Table \
    (
    'holidays',
    metadata,
    Column ('id',          Integer, primary_key=True),
    Column ('holiday_type',        String(20),  nullable=False),
    Column ('calendar_id', Integer, ForeignKey ('calendars.id')),
    Column ('description', String ),
    Column ('year',        Integer),
    Column ('month',       Integer),
    Column ('day',         Integer)
    )

holiday_mapper           = mapper (Holiday,          holidays_table, polymorphic_on=holidays_table.c.holiday_type, polymorphic_identity='holiday')
repeating_holiday_mapper = mapper (RepeatingHoliday, inherits=holiday_mapper, polymorphic_identity='repeating')
simple_holiday_mapper    = mapper (SimpleHoliday,    inherits=holiday_mapper, polymorphic_identity='simple')

products_table = Table \
    (
    'products',
    metadata,
    Column ('id', Integer, primary_key=True),
    Column ('product_type', String (20), nullable=False),
    Column ('name', String, nullable=False, unique=True),
    Column ('currency_id', Integer, ForeignKey ('products.id')),
    Column ('declaration_date', Date),
    Column ('ex_dividend_date', Date),
    Column ('record_date',      Date),
    Column ('pay_date',         Date),
    Column ('amount',           Float),
    Column ('factor',           Float)
    )

alias_specification_table = Table \
    (
    'alias_specifications',
    metadata,
    Column ('id',           Integer, primary_key=True),
    Column ('name',         String,  nullable=False, unique=True)
    )

product_mapper = \
    mapper \
        (
        Product,          
        products_table, 
        polymorphic_on=products_table.c.product_type, 
        polymorphic_identity='product'
        )
        
currency_product_mapper = \
    mapper \
        (
        Currency,  
        inherits=product_mapper, 
        polymorphic_identity='currency'
        )

stock_product_mapper = \
    mapper \
        (
        Stock,     
        inherits=product_mapper, 
        polymorphic_identity='stock'
        )

dividend_product_mapper = \
    mapper \
        (
        Dividend,     
        inherits=product_mapper, 
        polymorphic_identity='dividend'
        )

aliasspecification_mapper = \
    mapper \
        (
        TagSpecification,
        alias_specification_table
        )
        
metadata.create_all (engine)

def test():
    ny  = SimpleCalendar (code='NY',  name='New York', holidays=[SimpleHoliday ("New Year", 2011, 1, 1)])
    lon = SimpleCalendar (code='LON', name='London',   holidays=[SimpleHoliday ("New Year", 2011, 1, 1), RepeatingHoliday ('MayDay', 5, 1)])
    session.add (ny)
    session.add (lon)
    session.commit ()
    uc = UnionCalendar ([ny, lon])
    print uc
    print 'add'
    session.add (uc)
    session.flush()
    session.commit()
    print 'done'
    
    for c in uc.children:
        print c.code
    
    gbp = Currency ('GBP')
    session.add (gbp)
    session.commit()
    mks = Stock ('MKS', gbp)
    session.add (mks)
    session.commit()
    
    #aspec = TagSpecification ('ISIN')
    #session.add (aspec)
    #session.commit()

test()